# 数据导入导出增强服务
import pandas as pd
import json
import xml.etree.ElementTree as ET
from io import BytesIO, StringIO
from typing import Dict, List, Any, Optional, Tuple
from django.core.files.uploadedfile import UploadedFile
from django.contrib.auth.models import User
from django.utils import timezone
from django.db import transaction
import logging

from .models import Asset, AssetCategory, Department, Location, Supplier, Consumable, Warehouse

logger = logging.getLogger(__name__)


class DataImportExportService:
    """数据导入导出服务"""
    
    def __init__(self):
        self.supported_formats = ['csv', 'excel', 'json', 'xml']
        self.supported_models = {
            'assets': Asset,
            'categories': AssetCategory,
            'departments': Department,
            'locations': Location,
            'suppliers': Supplier,
            'consumables': Consumable,
            'warehouses': Warehouse,
        }
    
    def import_data(self, 
                   file: UploadedFile, 
                   model_name: str, 
                   format_type: str,
                   user: User,
                   options: Dict = None) -> Dict[str, Any]:
        """导入数据"""
        
        if format_type not in self.supported_formats:
            return {
                'success': False,
                'error': f'不支持的格式: {format_type}'
            }
        
        if model_name not in self.supported_models:
            return {
                'success': False,
                'error': f'不支持的模型: {model_name}'
            }
        
        try:
            # 解析文件数据
            data = self._parse_file(file, format_type)
            
            if not data:
                return {
                    'success': False,
                    'error': '文件为空或格式错误'
                }
            
            # 验证和清洗数据
            validation_result = self._validate_and_clean_data(data, model_name, options or {})
            
            if not validation_result['success']:
                return validation_result
            
            # 导入数据
            import_result = self._import_validated_data(
                validation_result['data'], 
                model_name, 
                user,
                options or {}
            )
            
            return import_result
            
        except Exception as e:
            logger.error(f"数据导入失败: {e}")
            return {
                'success': False,
                'error': f'导入失败: {str(e)}'
            }
    
    def export_data(self, 
                   model_name: str, 
                   format_type: str,
                   filters: Dict = None,
                   fields: List[str] = None) -> Dict[str, Any]:
        """导出数据"""
        
        if format_type not in self.supported_formats:
            return {
                'success': False,
                'error': f'不支持的格式: {format_type}'
            }
        
        if model_name not in self.supported_models:
            return {
                'success': False,
                'error': f'不支持的模型: {model_name}'
            }
        
        try:
            # 获取数据
            model_class = self.supported_models[model_name]
            queryset = model_class.objects.all()
            
            # 应用过滤器
            if filters:
                queryset = self._apply_filters(queryset, filters)
            
            # 转换为字典列表
            data = self._serialize_queryset(queryset, model_name, fields)
            
            # 生成文件内容
            file_content = self._generate_file_content(data, format_type)
            
            return {
                'success': True,
                'content': file_content,
                'filename': f'{model_name}_export_{timezone.now().strftime("%Y%m%d_%H%M%S")}.{format_type}',
                'content_type': self._get_content_type(format_type),
                'count': len(data)
            }
            
        except Exception as e:
            logger.error(f"数据导出失败: {e}")
            return {
                'success': False,
                'error': f'导出失败: {str(e)}'
            }
    
    def _parse_file(self, file: UploadedFile, format_type: str) -> List[Dict]:
        """解析文件内容"""
        
        if format_type == 'csv':
            return self._parse_csv(file)
        elif format_type == 'excel':
            return self._parse_excel(file)
        elif format_type == 'json':
            return self._parse_json(file)
        elif format_type == 'xml':
            return self._parse_xml(file)
        else:
            raise ValueError(f'不支持的格式: {format_type}')
    
    def _parse_csv(self, file: UploadedFile) -> List[Dict]:
        """解析CSV文件"""
        try:
            # 尝试不同的编码
            encodings = ['utf-8', 'gbk', 'gb2312', 'utf-8-sig']
            
            for encoding in encodings:
                try:
                    file.seek(0)
                    content = file.read().decode(encoding)
                    df = pd.read_csv(StringIO(content))
                    return df.to_dict('records')
                except UnicodeDecodeError:
                    continue
            
            raise ValueError('无法解析CSV文件编码')
            
        except Exception as e:
            logger.error(f"解析CSV文件失败: {e}")
            raise
    
    def _parse_excel(self, file: UploadedFile) -> List[Dict]:
        """解析Excel文件"""
        try:
            file.seek(0)
            df = pd.read_excel(file, engine='openpyxl')
            return df.to_dict('records')
        except Exception as e:
            logger.error(f"解析Excel文件失败: {e}")
            raise
    
    def _parse_json(self, file: UploadedFile) -> List[Dict]:
        """解析JSON文件"""
        try:
            file.seek(0)
            content = file.read().decode('utf-8')
            data = json.loads(content)
            
            # 如果是单个对象，转换为列表
            if isinstance(data, dict):
                return [data]
            elif isinstance(data, list):
                return data
            else:
                raise ValueError('JSON格式错误')
                
        except Exception as e:
            logger.error(f"解析JSON文件失败: {e}")
            raise
    
    def _parse_xml(self, file: UploadedFile) -> List[Dict]:
        """解析XML文件"""
        try:
            file.seek(0)
            content = file.read().decode('utf-8')
            root = ET.fromstring(content)
            
            data = []
            for item in root:
                record = {}
                for child in item:
                    record[child.tag] = child.text
                data.append(record)
            
            return data
            
        except Exception as e:
            logger.error(f"解析XML文件失败: {e}")
            raise
    
    def _validate_and_clean_data(self, 
                                data: List[Dict], 
                                model_name: str, 
                                options: Dict) -> Dict[str, Any]:
        """验证和清洗数据"""
        
        valid_data = []
        errors = []
        warnings = []
        
        # 获取模型字段配置
        field_config = self._get_model_field_config(model_name)
        
        for i, record in enumerate(data):
            try:
                # 清洗数据
                cleaned_record = self._clean_record(record, field_config, options)
                
                # 验证数据
                validation_errors = self._validate_record(cleaned_record, field_config, model_name)
                
                if validation_errors:
                    errors.append({
                        'row': i + 1,
                        'errors': validation_errors,
                        'data': record
                    })
                else:
                    valid_data.append(cleaned_record)
                    
            except Exception as e:
                errors.append({
                    'row': i + 1,
                    'errors': [str(e)],
                    'data': record
                })
        
        # 检查是否有致命错误
        if errors and not options.get('ignore_errors', False):
            return {
                'success': False,
                'error': '数据验证失败',
                'errors': errors,
                'warnings': warnings,
                'valid_count': len(valid_data),
                'error_count': len(errors)
            }
        
        return {
            'success': True,
            'data': valid_data,
            'errors': errors,
            'warnings': warnings,
            'valid_count': len(valid_data),
            'error_count': len(errors)
        }
    
    def _clean_record(self, record: Dict, field_config: Dict, options: Dict) -> Dict:
        """清洗单条记录"""
        cleaned = {}
        
        for field_name, field_info in field_config.items():
            raw_value = record.get(field_name) or record.get(field_info.get('alias', ''))
            
            if raw_value is None or raw_value == '':
                if field_info.get('required', False):
                    cleaned[field_name] = None  # 将在验证阶段处理
                continue
            
            # 根据字段类型清洗数据
            field_type = field_info.get('type', 'str')
            
            try:
                if field_type == 'int':
                    cleaned[field_name] = int(float(str(raw_value)))
                elif field_type == 'float':
                    cleaned[field_name] = float(str(raw_value))
                elif field_type == 'bool':
                    cleaned[field_name] = self._parse_boolean(raw_value)
                elif field_type == 'date':
                    cleaned[field_name] = pd.to_datetime(raw_value).date()
                elif field_type == 'datetime':
                    cleaned[field_name] = pd.to_datetime(raw_value)
                else:
                    cleaned[field_name] = str(raw_value).strip()
                    
            except (ValueError, TypeError) as e:
                # 数据类型转换失败，保留原值，在验证阶段处理
                cleaned[field_name] = raw_value
        
        return cleaned
    
    def _parse_boolean(self, value) -> bool:
        """解析布尔值"""
        if isinstance(value, bool):
            return value
        
        str_value = str(value).lower().strip()
        return str_value in ['true', '1', 'yes', 'y', '是', '真']
    
    def _validate_record(self, record: Dict, field_config: Dict, model_name: str) -> List[str]:
        """验证单条记录"""
        errors = []
        
        # 检查必填字段
        for field_name, field_info in field_config.items():
            if field_info.get('required', False):
                if field_name not in record or record[field_name] is None:
                    errors.append(f'字段 {field_name} 是必填的')
        
        # 检查唯一性约束
        unique_fields = [name for name, info in field_config.items() if info.get('unique', False)]
        for field_name in unique_fields:
            if field_name in record and record[field_name]:
                if self._check_field_exists(model_name, field_name, record[field_name]):
                    errors.append(f'字段 {field_name} 的值 "{record[field_name]}" 已存在')
        
        # 检查外键关系
        for field_name, field_info in field_config.items():
            if field_info.get('type') == 'foreign_key' and field_name in record:
                if not self._validate_foreign_key(field_info['related_model'], record[field_name]):
                    errors.append(f'外键字段 {field_name} 的值 "{record[field_name]}" 不存在')
        
        return errors
    
    def _check_field_exists(self, model_name: str, field_name: str, value: Any) -> bool:
        """检查字段值是否已存在"""
        model_class = self.supported_models[model_name]
        return model_class.objects.filter(**{field_name: value}).exists()
    
    def _validate_foreign_key(self, related_model_name: str, value: Any) -> bool:
        """验证外键关系"""
        if related_model_name in self.supported_models:
            related_model = self.supported_models[related_model_name]
            return related_model.objects.filter(pk=value).exists()
        return True
    
    def _import_validated_data(self, 
                              data: List[Dict], 
                              model_name: str, 
                              user: User,
                              options: Dict) -> Dict[str, Any]:
        """导入验证过的数据"""
        
        model_class = self.supported_models[model_name]
        created_count = 0
        updated_count = 0
        errors = []
        
        update_mode = options.get('update_mode', 'create_only')  # create_only, update_only, create_or_update
        batch_size = options.get('batch_size', 100)
        
        try:
            with transaction.atomic():
                for i in range(0, len(data), batch_size):
                    batch = data[i:i + batch_size]
                    
                    for record in batch:
                        try:
                            if update_mode == 'create_only':
                                obj = model_class.objects.create(**record)
                                created_count += 1
                            elif update_mode == 'update_only':
                                # 需要指定更新条件
                                update_fields = options.get('update_fields', ['id'])
                                filter_kwargs = {field: record[field] for field in update_fields if field in record}
                                
                                if filter_kwargs:
                                    updated = model_class.objects.filter(**filter_kwargs).update(**record)
                                    updated_count += updated
                            else:  # create_or_update
                                unique_fields = options.get('unique_fields', ['id'])
                                filter_kwargs = {field: record[field] for field in unique_fields if field in record}
                                
                                if filter_kwargs:
                                    obj, created = model_class.objects.update_or_create(
                                        defaults=record,
                                        **filter_kwargs
                                    )
                                    if created:
                                        created_count += 1
                                    else:
                                        updated_count += 1
                                else:
                                    obj = model_class.objects.create(**record)
                                    created_count += 1
                                    
                        except Exception as e:
                            errors.append({
                                'record': record,
                                'error': str(e)
                            })
                            
                            if not options.get('ignore_errors', False):
                                raise
            
            # 记录导入日志
            self._log_import_activity(user, model_name, created_count, updated_count, len(errors))
            
            return {
                'success': True,
                'created_count': created_count,
                'updated_count': updated_count,
                'error_count': len(errors),
                'errors': errors,
                'message': f'导入完成: 创建 {created_count} 条，更新 {updated_count} 条，错误 {len(errors)} 条'
            }
            
        except Exception as e:
            logger.error(f"批量导入数据失败: {e}")
            return {
                'success': False,
                'error': f'导入失败: {str(e)}',
                'created_count': created_count,
                'updated_count': updated_count,
                'error_count': len(errors)
            }
    
    def _get_model_field_config(self, model_name: str) -> Dict:
        """获取模型字段配置"""
        
        configs = {
            'assets': {
                'name': {'type': 'str', 'required': True, 'alias': '资产名称'},
                'asset_number': {'type': 'str', 'required': True, 'unique': True, 'alias': '资产编号'},
                'description': {'type': 'str', 'alias': '描述'},
                'category_id': {'type': 'foreign_key', 'related_model': 'categories', 'alias': '类别ID'},
                'department_id': {'type': 'foreign_key', 'related_model': 'departments', 'alias': '部门ID'},
                'purchase_price': {'type': 'float', 'alias': '购买价格'},
                'purchase_date': {'type': 'date', 'alias': '购买日期'},
                'status': {'type': 'str', 'alias': '状态'},
                # 虚拟导出字段：所属组织（通过资产账簿关联）
                'organization': {'type': 'str', 'alias': '所属组织'},
            },
            'categories': {
                'name': {'type': 'str', 'required': True, 'unique': True, 'alias': '类别名称'},
                'description': {'type': 'str', 'alias': '描述'},
            },
            'departments': {
                'name': {'type': 'str', 'required': True, 'unique': True, 'alias': '部门名称'},
                'description': {'type': 'str', 'alias': '描述'},
            },
            'suppliers': {
                'name': {'type': 'str', 'required': True, 'alias': '供应商名称'},
                'contact_person': {'type': 'str', 'alias': '联系人'},
                'phone': {'type': 'str', 'alias': '电话'},
                'email': {'type': 'str', 'alias': '邮箱'},
                'address': {'type': 'str', 'alias': '地址'},
            },
            'consumables': {
                'name': {'type': 'str', 'required': True, 'alias': '耗材名称'},
                'warehouse_id': {'type': 'foreign_key', 'related_model': 'warehouses', 'alias': '仓库ID'},
                'quantity': {'type': 'int', 'required': True, 'alias': '数量'},
                'min_quantity': {'type': 'int', 'alias': '最小库存'},
                'unit_price': {'type': 'float', 'alias': '单价'},
            }
        }
        
        return configs.get(model_name, {})
    
    def _apply_filters(self, queryset, filters: Dict):
        """应用过滤器"""
        for field, value in filters.items():
            if value is not None:
                queryset = queryset.filter(**{field: value})
        return queryset
    
    def _serialize_queryset(self, queryset, model_name: str, fields: List[str] = None) -> List[Dict]:
        """序列化查询集"""
        data = []
        
        for obj in queryset:
            record = {}
            
            if fields:
                # 只导出指定字段
                for field in fields:
                    if hasattr(obj, field):
                        value = getattr(obj, field)
                        record[field] = self._serialize_value(value)
                    # 支持资产的虚拟字段: 组织名称
                    elif model_name == 'assets' and field in ['organization', 'organization_name']:
                        org_name = None
                        try:
                            if getattr(obj, 'ledger', None) and getattr(obj.ledger, 'organization', None):
                                org_name = obj.ledger.organization.name
                        except Exception:
                            org_name = None
                        record[field] = org_name
            else:
                # 导出所有字段
                for field in obj._meta.fields:
                    field_name = field.name
                    value = getattr(obj, field_name)
                    record[field_name] = self._serialize_value(value)
                # 对资产模型追加组织名称，便于对账和阅读
                if model_name == 'assets':
                    org_name = None
                    try:
                        if getattr(obj, 'ledger', None) and getattr(obj.ledger, 'organization', None):
                            org_name = obj.ledger.organization.name
                    except Exception:
                        org_name = None
                    # 统一使用 'organization' 作为列名
                    record['organization'] = org_name
            
            data.append(record)
        
        return data
    
    def _serialize_value(self, value) -> Any:
        """序列化字段值"""
        if value is None:
            return None
        elif hasattr(value, 'isoformat'):  # datetime/date
            return value.isoformat()
        elif hasattr(value, '__str__'):
            return str(value)
        else:
            return value
    
    def _generate_file_content(self, data: List[Dict], format_type: str) -> bytes:
        """生成文件内容"""
        
        if format_type == 'csv':
            return self._generate_csv_content(data)
        elif format_type == 'excel':
            return self._generate_excel_content(data)
        elif format_type == 'json':
            return self._generate_json_content(data)
        elif format_type == 'xml':
            return self._generate_xml_content(data)
        else:
            raise ValueError(f'不支持的格式: {format_type}')
    
    def _generate_csv_content(self, data: List[Dict]) -> bytes:
        """生成CSV内容"""
        if not data:
            return b''
        
        df = pd.DataFrame(data)
        output = StringIO()
        df.to_csv(output, index=False, encoding='utf-8-sig')
        return output.getvalue().encode('utf-8-sig')
    
    def _generate_excel_content(self, data: List[Dict]) -> bytes:
        """生成Excel内容"""
        if not data:
            return b''
        
        df = pd.DataFrame(data)
        output = BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='Data')
        
        output.seek(0)
        return output.getvalue()
    
    def _generate_json_content(self, data: List[Dict]) -> bytes:
        """生成JSON内容"""
        return json.dumps(data, ensure_ascii=False, indent=2, default=str).encode('utf-8')
    
    def _generate_xml_content(self, data: List[Dict]) -> bytes:
        """生成XML内容"""
        root = ET.Element('data')
        
        for record in data:
            item = ET.SubElement(root, 'item')
            for key, value in record.items():
                field = ET.SubElement(item, key)
                field.text = str(value) if value is not None else ''
        
        return ET.tostring(root, encoding='utf-8', xml_declaration=True)
    
    def _get_content_type(self, format_type: str) -> str:
        """获取内容类型"""
        content_types = {
            'csv': 'text/csv',
            'excel': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'json': 'application/json',
            'xml': 'application/xml'
        }
        return content_types.get(format_type, 'application/octet-stream')
    
    def _log_import_activity(self, user: User, model_name: str, created: int, updated: int, errors: int):
        """记录导入活动日志"""
        try:
            from .models_system import SystemLog
            
            SystemLog.objects.create(
                level='INFO',
                message=f'数据导入: {model_name}',
                details={
                    'user': user.username,
                    'model': model_name,
                    'created_count': created,
                    'updated_count': updated,
                    'error_count': errors,
                    'timestamp': timezone.now().isoformat()
                }
            )
        except Exception as e:
            logger.error(f"记录导入日志失败: {e}")


# 全局服务实例
import_export_service = DataImportExportService()